﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2012/2/23 19:22:30
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sector7.Restaurant.EntityType;
using Sector7.Framework.Core;
using System.Data.SqlClient;
using Sector7.Framework.Utility;
using System.Data;
using Sector7.Restaurant.DAO.Utility;
using Sector7.Framework.Enums;
using System.Collections;
using Sector7.Framework.DB;

namespace Sector7.Restaurant.DAO.OleDB
{
    /// <summary>
    /// 销售单数据访问对象
    /// </summary>
    public class SalesBillDAO : ISalesBillDAO
    {
        #region Public functions
        public int Create(RS_SalesBill bill)
        {
            using (DBHelper opt = new DBHelper())
            {
                string sql = 
                    @"INSERT INTO RS_SalesBill
                            (BillId
                            ,OperatorId
                            ,CheckerId
                            ,Amount
                            ,OccurTime
                            ,Status
                            ,Remarks
                            ,TableId)
                        VALUES
                            (@BillId
                            ,@OperatorId
                            ,@CheckerId
                            ,@Amount
                            ,@OccurTime
                            ,@Status
                            ,@Remarks
                            ,@TableId)";

                return opt.ExecuteNonQuery(sql,
                    DBUtil.CreateSqlParameter(bill.BillId, "BillId"),
                    DBUtil.CreateSqlParameter(bill.OperatorId, "OperatorId"),
                    DBUtil.CreateSqlParameter(bill.CheckerId, "CheckerId"),
                    DBUtil.CreateSqlParameter(bill.Amount, "Amount"),
                    DBUtil.CreateSqlParameter(bill.OccurTime, "OccurTime"),
                    DBUtil.CreateSqlParameter(bill.Status, "Status"),
                    DBUtil.CreateSqlParameter(bill.Remarks, "Remarks"),
                    DBUtil.CreateSqlParameter(bill.TableId, "TableId"));
            }
        }

        public RS_SalesBill Read(int tableId)
        {
            using (DBHelper opt = new DBHelper())
            {
                string sql = string.Format(
                    @"SELECT
                            *
                        FROM RS_SalesBill AS bill
                            LEFT JOIN RS_Table AS diningTable
                                ON bill.TableId = diningTable.TableId
                            LEFT JOIN RS_Room AS room
                                ON room.RoomId = diningTable.RoomId
                        WHERE
                            bill.TableId = @TableId AND bill.Status = {0}", (int)BillStatusType.Inprogress);

                SqlDataReader reader = opt.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(tableId, "TableId"));

                RS_SalesBill bill = null;

                if (reader.Read())
                {
                    object __table = new RS_Table();
                    DBUtil.ReadData(reader, ref __table);
                    RS_Table table = __table as RS_Table;

                    object __bill = new RS_SalesBill();
                    DBUtil.ReadData(reader, ref __bill);
                    bill = __bill as RS_SalesBill;
                    bill.Table = table as RS_Table;

                    ISalesBillItemDAO itemsDAO = new SalesBillItemDAO();
                    bill.Items = itemsDAO.ReadAll(bill.BillId);
                }

                return bill;
            }
        }

        public int Update(EntityDelta delta)
        {
            int ret = -1;

            using (DBHelper opt = new DBHelper())
            {
                ret = opt.ExecuteNonQuery(
                    DBUtil.CreateUpdateSql(delta),
                    DBUtil.CreateUpdateParameters(delta));
            }

            return ret;
        }

        /********************************************************/

        //public string Create(RS_SalesBill bill)
        //{
        //    using (DBHelper opt = new DBHelper())
        //    {
        //        string storedProcedureName = "RP_InsertSalesBill";

        //        SqlParameter seed = DBHelper.CreateDirectionParameter("@ReturnValue", DbType.Int32, ParameterDirection.ReturnValue);
        //        opt.ExecuteStoredProcedureNonQuery(
        //            storedProcedureName,
        //            DBHelper.CreateSqlParameter(bill.BillId, "BillId"),
        //            DBHelper.CreateSqlParameter(bill.SalesDateTime, "SalesDateTime"),
        //            DBHelper.CreateSqlParameter(bill.IsValid, "IsValid"),
        //            DBHelper.CreateSqlParameter(bill.WaiterId, "WaiterId"),
        //            DBHelper.CreateSqlParameter(bill.CustomerId, "CustomerId"),
        //            DBHelper.CreateSqlParameter(bill.TableId, "TableId"),
        //            DBHelper.CreateSqlParameter(bill.IsDone, "IsDone"),
        //            seed);

        //        return bill.BillId + StringUtil.PadLeft(seed.Value, 3, '0');
        //    }
        //}

        public RS_SalesBill Read(string billId)
        {
            using (DBHelper opt = new DBHelper())
            {
                string sql =
                    "SELECT *                                       " +
                    "   FROM RS_SalesBill AS bill                   " +
                    "   LEFT JOIN RS_DiningTable AS diningTable     " +
                    "       ON bill.TableId = diningTable.TableId   " +
                    "   LEFT JOIN RS_PrivateRoom AS room            " +
                    "       ON room.RoomId = diningTable.RoomId     " +
                    "   WHERE bill.BillId = @BillId                 ";

                SqlDataReader reader = opt.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(billId, "BillId"));

                object bill = new RS_SalesBill();

                if (reader.Read())
                {
                    DBUtil.ReadData(reader, ref bill);
                    object table = new RS_Table();

                    DBUtil.ReadData(reader, ref table);
                    (bill as RS_SalesBill).Table = table as RS_Table;

                    ISalesBillItemDAO itemsDAO = new SalesBillItemDAO();
                    (bill as RS_SalesBill).Items = itemsDAO.ReadAll(billId);
                }

                return bill as RS_SalesBill;
            }
        }

        public List<RS_SalesBill> ReadAll(bool isDone)
        {
            using (DBHelper opt = new DBHelper())
            {
                List<RS_SalesBill> bills = new List<RS_SalesBill>();

                string sql =
                    "SELECT *                                       " +
                    "   FROM RS_SalesBill AS bill                   " +
                    "   LEFT JOIN RS_DiningTable AS diningTable     " +
                    "       ON bill.TableId = diningTable.TableId   " +
                    "   LEFT JOIN RS_PrivateRoom AS room            " +
                    "       ON room.RoomId = diningTable.RoomId     " +
                    "   WHERE bill.IsDone = @IsDone                 ";

                SqlDataReader reader = opt.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(isDone, "IsDone"));
                
                while (reader.Read())
                {
                    object table = new RS_Table();
                    DBUtil.ReadData(reader, ref table);

                    object bill = new RS_SalesBill();
                    DBUtil.ReadData(reader, ref bill);
                    (bill as RS_SalesBill).Table = table as RS_Table;

                    ISalesBillItemDAO itemsDAO = new SalesBillItemDAO();
                    (bill as RS_SalesBill).Items = isDone ? itemsDAO.ReadAll((bill as RS_SalesBill).BillId) : itemsDAO.ReadAll((bill as RS_SalesBill).BillId, 2);

                    bills.Add(bill as RS_SalesBill);
                }

                return bills;
            }
        }

        public List<RS_SalesBill> ReadAll(params int[] billIds)
        {
            using (DBHelper opt = new DBHelper())
            {
                List<RS_SalesBill> bills = new List<RS_SalesBill>();

                string sql =
                    "SELECT *                                       " +
                    "   FROM RS_SalesBill AS bill                   " +
                    "   LEFT JOIN RS_DiningTable AS diningTable     " +
                    "       ON bill.TableId = diningTable.TableId   " +
                    "   LEFT JOIN RS_PrivateRoom AS room            " +
                    "       ON room.RoomId = diningTable.RoomId     " +
                    "   WHERE bill.IsDone = 0                       ";

                foreach (var billId in billIds)
                {
                    sql += string.Format("      AND bill.TableId = {0}", billId);
                }

                SqlDataReader reader = opt.ExecuteQuery(sql);

                while (reader.Read())
                {
                    object table = new RS_Table();
                    DBUtil.ReadData(reader, ref table);

                    object bill = new RS_SalesBill();
                    DBUtil.ReadData(reader, ref bill);
                    (bill as RS_SalesBill).Table = table as RS_Table;

                    ISalesBillItemDAO itemsDAO = new SalesBillItemDAO();
                    (bill as RS_SalesBill).Items = itemsDAO.ReadAll((bill as RS_SalesBill).BillId);

                    bills.Add(bill as RS_SalesBill);
                }

                return bills;
            }
        }

        public DBOptResultType Delete(string billId)
        {
            throw new NotImplementedException();
        }
        #endregion
    }
}
